CREATE PROCEDURE [dbo].[GenerateDataDictionary]
-- =============================================
-- Author:		Corey Furman
-- Create date: 20 Nov 2008
-- Description:	Generates a data dictionary
-- =============================================
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON

	/* variable set up */
	DECLARE @LineFeed	AS VARCHAR(2)
	DECLARE @Tab		AS CHAR
	DECLARE @SpaceChar	AS CHAR
	DECLARE @WhiteSpace AS VARCHAR(4)
	SET @LineFeed		= CHAR(10) + CHAR(13)
	SET @Tab			= CHAR(9)
	SET @SpaceChar		= CHAR(32)
	SET @WhiteSpace		= @LineFeed + @Tab + @SpaceChar
	DECLARE @Period		AS CHAR
	SET @Period			= '.'
	

	/* table listing */
	SELECT DISTINCT
			[TableName]		= T.Name,
			[Description]	= COALESCE(E.value, ''),
			[Type]			= 'Table'
	FROM sys.tables T
	LEFT JOIN sys.extended_properties E ON E.major_id = T.object_id
		AND E.minor_id = 0
		AND E.name NOT LIKE 'MS_DiagramPane%'
	WHERE T.name NOT IN (SELECT ObjectName FROM dbo.DataDictionaryExclusions WHERE ObjectType = 'Table')
	
	UNION
	
	SELECT  DISTINCT
			[TableName]		= V.Name,
			[Description]	= COALESCE(E.value, ''),
			[Type]			= 'View'
	FROM sys.views V
	LEFT JOIN sys.extended_properties E ON E.major_id = V.object_id
		AND E.minor_id = 0
		AND E.name NOT LIKE 'MS_DiagramPane%'
	WHERE V.name NOT IN (SELECT ObjectName FROM dbo.DataDictionaryExclusions WHERE ObjectType = 'View')
	ORDER BY T.Name

	/* column listing */
	SELECT	DISTINCT
			[Column]		= C.COLUMN_NAME,
			[Table]			= C.TABLE_NAME,
			[Position]		= C.ORDINAL_POSITION,
			DataType		= CASE	WHEN C.DATA_TYPE = 'varchar' AND C.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'varchar(max)'
									WHEN C.DATA_TYPE = 'nvarchar' AND C.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'nvarchar(max)'
									WHEN C.DATA_TYPE = 'varbinary' AND C.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'varbinary(max)'
									WHEN C.DATA_TYPE = 'varchar' THEN C.DATA_TYPE + '(' + CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
									WHEN C.DATA_TYPE = 'nvarchar' THEN C.DATA_TYPE + '(' + CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
									WHEN C.DATA_TYPE = 'varbinary' THEN C.DATA_TYPE + '(' + CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
									WHEN C.DATA_TYPE = 'text' THEN C.DATA_TYPE + '(' + CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
									WHEN C.DATA_TYPE = 'ntext' THEN C.DATA_TYPE + '(' + CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
									WHEN C.DATA_TYPE = 'char' THEN C.DATA_TYPE + '(' + CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
									WHEN C.DATA_TYPE = 'numeric' THEN C.DATA_TYPE + '(' + CAST(C.NUMERIC_PRECISION AS VARCHAR(10)) + ', ' + CAST(C.NUMERIC_SCALE AS VARCHAR(10)) + ')'
									WHEN C.DATA_TYPE = 'decimal' THEN C.DATA_TYPE + '(' + CAST(C.NUMERIC_PRECISION AS VARCHAR(10)) + ', ' + CAST(C.NUMERIC_SCALE AS VARCHAR(10)) + ')'
									WHEN C.DATA_TYPE = 'float' THEN C.DATA_TYPE + '(' + CAST(C.NUMERIC_PRECISION AS VARCHAR(10)) + ', ' + CAST(C.NUMERIC_SCALE AS VARCHAR(10)) + ')'
							  ELSE C.DATA_TYPE
							  END,
			DefaultValue	= COALESCE(C.COLUMN_DEFAULT, ''),
			Nullable		= C.IS_NULLABLE,
			[Description]	= COALESCE(E.value, '')
	FROM INFORMATION_SCHEMA.columns C
	LEFT JOIN sys.syscolumns C2 ON C2.name = C.COLUMN_NAME
	LEFT JOIN sys.extended_properties E ON E.major_id = C2.id
		AND E.minor_id = C2.colid
	WHERE C.TABLE_NAME NOT IN (SELECT ObjectName FROM dbo.DataDictionaryExclusions WHERE ObjectType IN ('Table', 'View'))
	ORDER BY C.COLUMN_NAME, C.TABLE_NAME

	/* relationships */
	SELECT	[Constraint]		= T.CONSTRAINT_NAME,
			[Type]				= LOWER(REPLACE(T.CONSTRAINT_TYPE, ' KEY', '')),
			[Table]				= T.TABLE_NAME,
			[Column]			= C1.COLUMN_NAME,
			ConstrainedBy		= CASE WHEN LOWER(REPLACE(T.CONSTRAINT_TYPE, ' KEY', '')) = 'foreign' THEN C2.TABLE_NAME + '.' + C2.COLUMN_NAME
								  ELSE ''
								  END,
			[Description]		= CASE WHEN LOWER(REPLACE(T.CONSTRAINT_TYPE, ' KEY', '')) = 'primary' THEN COALESCE(E1.value, '')
								  ELSE COALESCE(E2.value, '')
								  END,
			[DeleteAction]		= LOWER(REPLACE(COALESCE(F.delete_referential_action_desc, ''), '_', ' ')),
			[UpdateAction]		= LOWER(REPLACE(COALESCE(F.update_referential_action_desc, ''), '_', ' '))
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
	JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C1 ON C1.CONSTRAINT_NAME = T.CONSTRAINT_NAME
	LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON R.CONSTRAINT_NAME = T.CONSTRAINT_NAME
	LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C2 ON C2.CONSTRAINT_NAME = R.UNIQUE_CONSTRAINT_NAME
	LEFT JOIN sys.key_constraints K ON K.name = T.CONSTRAINT_NAME
	LEFT JOIN sys.extended_properties E1 ON E1.major_id = K.object_id
	LEFT JOIN sys.foreign_keys F ON F.name = T.CONSTRAINT_NAME
	LEFT JOIN sys.extended_properties E2 ON E2.major_id = F.object_id
	WHERE T.TABLE_NAME NOT IN (SELECT ObjectName FROM dbo.DataDictionaryExclusions WHERE ObjectType = 'Table')
	ORDER BY T.TABLE_NAME, T.CONSTRAINT_TYPE DESC

	/* stored procedures */
	SELECT name
	FROM sys.procedures
	WHERE name NOT IN (SELECT ObjectName FROM dbo.DataDictionaryExclusions WHERE ObjectType = 'Procedure')
	ORDER BY name

	/* procedures to object cross reference */
	SELECT * FROM (
		SELECT DISTINCT
			[SProc Name]	= P.name,
			[Object Name]	= O.name,
			[Obj Type]		= CASE	WHEN O.xtype = 'u'  THEN 'Table'
									WHEN O.xtype = 'v'  THEN 'View'
									WHEN O.xtype = 'p'  THEN 'Procedure'
									WHEN O.xtype = 'af' THEN 'Aggregate Function'
									WHEN O.xtype = 'if' THEN 'Inline Table Function'
									WHEN O.xtype = 'tf' THEN 'Table Function'
									WHEN O.xtype = 'fn' THEN 'Scalar Function'
									WHEN O.xtype = 'tr' THEN 'Trigger'
							  END
		FROM sysobjects O
		INNER JOIN (
			SELECT A.name, B.text
			FROM sysobjects A INNER JOIN syscomments B ON A.id = B.id
		) P ON P.text LIKE '%[' + @WhiteSpace + @Period + ']' + O.name + '[' + @WhiteSpace + ']%'
			AND O.xtype IN ('u', 'v', 'p', 'af', 'if', 'tf', 'fn', 'tr')
			AND P.name <> O.name
	) data
	WHERE [SProc Name] NOT IN (SELECT ObjectName FROM dbo.DataDictionaryExclusions WHERE ObjectType = 'Procedure')
		AND [Object Name] NOT IN (SELECT ObjectName FROM dbo.DataDictionaryExclusions WHERE ObjectType = 'Table')
	ORDER BY [SProc Name], [Obj Type], [Object Name]

	/* triggers */
	SELECT	T.name,
			[Type]			= LOWER(TE.type_desc),
			[Parent]		= O.name
	FROM sys.triggers T
	JOIN sys.trigger_events TE ON TE.object_id = T.object_id
	JOIN sys.objects O ON O.object_id = T.parent_id
	WHERE O.name NOT IN (SELECT ObjectName FROM dbo.DataDictionaryExclusions WHERE ObjectType = 'Table')
		AND O.name NOT IN (SELECT ObjectName FROM dbo.DataDictionaryExclusions WHERE ObjectType = 'View')
	ORDER BY O.name, TE.type_desc
	
	/* parameters */
	SELECT	[Parent Name]	= O.name,
			[Param Name]	= P.name,
			[Direction]		= CASE	WHEN P.is_output = 0 THEN 'Input' ELSE 'Output' END,
			[Position]		= P.parameter_id,
			DataType		= CASE	WHEN T.name = 'varchar' AND P.max_length = -1 THEN 'varchar(max)'
									WHEN T.name = 'nvarchar' AND P.max_length = -1 THEN 'nvarchar(max)'
									WHEN T.name = 'varbinary' AND P.max_length = -1 THEN 'varbinary(max)'
									WHEN T.name = 'varchar' THEN T.name + '(' + CAST(P.max_length AS VARCHAR(10)) + ')'
									WHEN T.name = 'nvarchar' THEN T.name + '(' + CAST(P.max_length AS VARCHAR(10)) + ')'
									WHEN T.name = 'varbinary' THEN T.name + '(' + CAST(P.max_length AS VARCHAR(10)) + ')'
									WHEN T.name = 'text' THEN T.name + '(' + CAST(P.max_length AS VARCHAR(10)) + ')'
									WHEN T.name = 'ntext' THEN T.name + '(' + CAST(P.max_length AS VARCHAR(10)) + ')'
									WHEN T.name = 'char' THEN T.name + '(' + CAST(P.max_length AS VARCHAR(10)) + ')'
									WHEN T.name = 'numeric' THEN T.name + '(' + CAST(P.precision AS VARCHAR(10)) + ', ' + CAST(P.scale AS VARCHAR(10)) + ')'
									WHEN T.name = 'decimal' THEN T.name + '(' + CAST(P.precision AS VARCHAR(10)) + ', ' + CAST(P.scale AS VARCHAR(10)) + ')'
									WHEN T.name = 'float' THEN T.name + '(' + CAST(P.precision AS VARCHAR(10)) + ', ' + CAST(P.scale AS VARCHAR(10)) + ')'
							  ELSE T.name
							  END
	FROM sys.parameters P
	JOIN sys.types T ON T.user_type_id = P.user_type_id
	JOIN sys.objects O ON O.object_id = P.object_id
	WHERE O.name NOT IN (SELECT ObjectName FROM dbo.DataDictionaryExclusions WHERE ObjectType = 'Procedure')
		AND P.name NOT IN (SELECT ObjectName FROM dbo.DataDictionaryExclusions WHERE ObjectType = 'Function')
	ORDER BY O.name, P.is_output DESC, P.parameter_id

END;

CREATE PROCEDURE [dbo].[GenerateDataDictionaryExclusionList]
-- =============================================
-- Author:		Corey Furman
-- Create date: 2 Feb 2009
-- Description:	retrieves a list of objects that can be
--              ignored when a data dictionary is created
-- =============================================
AS
BEGIN

	SELECT * FROM (
		SELECT	[Object Name]	= O.name,
				[Object Type]	= CASE	WHEN O.type IN ('fn', 'af', 'if', 'tf') THEN 'Function'
										WHEN O.type = 'u'  THEN 'Table'
										WHEN O.type = 'v'  THEN 'View'
										WHEN O.type = 'p'  THEN 'Procedure'
										WHEN O.type = 'tr' THEN 'Trigger'
								  END,
				[Is Excluded]	= CAST(CASE	WHEN D.ObjectName IS NULL THEN 0 ELSE 1 END AS BIT)
		FROM sys.objects O
		LEFT JOIN dbo.DataDictionaryExclusions D ON D.ObjectName = O.name
		WHERE type_desc NOT IN ('SYSTEM_TABLE', 'DEFAULT_CONSTRAINT', 'FOREIGN_KEY_CONSTRAINT', 'PRIMARY_KEY_CONSTRAINT',
								 'UNIQUE_CONSTRAINT', 'INTERNAL_TABLE', 'SERVICE_QUEUE', 'EXTENDED_STORED_PROCEDURE')
	) data
	WHERE [Object Type] IS NOT NULL
	ORDER BY [Is Excluded] DESC, [Object Name]

END;

CREATE PROCEDURE GenerateDataDictionaryAddExclusion 
-- =============================================
-- Author:		Corey Furman
-- Create date: 20 Nov 2008
-- Description:	Adds or deletes an excluded object
-- =============================================
	@ObjectName AS VARCHAR(100),
	@ObjectType AS VARCHAR(25)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON

    -- Insert statements for procedure here
	DECLARE @Found AS INT

	SELECT @Found = COUNT(*)
	FROM dbo.DataDictionaryExclusions
	WHERE ObjectName = @ObjectName
		AND ObjectType = @ObjectType

	IF @Found = 0
		BEGIN
			INSERT INTO dbo.DataDictionaryExclusions (ObjectName, ObjectType)
			VALUES (@ObjectName, @ObjectType)
		END

	ELSE
		BEGIN
			DELETE FROM dbo.DataDictionaryExclusions
			WHERE ObjectName = @ObjectName
				AND ObjectType = @ObjectType
		END

END;

CREATE TABLE [dbo].[DataDictionaryExclusions](
    [ExclusionID] [int] IDENTITY(1,1) NOT NULL,
    [ObjectName] [varchar](100) NOT NULL,
    [ObjectType] [varchar](25) NOT NULL,
 CONSTRAINT [PK_DataDictionaryExclusions] PRIMARY KEY CLUSTERED
(
    [ExclusionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY];

INSERT INTO DataDictionaryExclusions (ObjectName, ObjectType)
VALUES	('DataDictionaryExclusions', 'Table');

INSERT INTO DataDictionaryExclusions (ObjectName, ObjectType)
VALUES	('GenerateDataDictionary', 'Procedure');

INSERT INTO DataDictionaryExclusions (ObjectName, ObjectType)
VALUES	('GenerateDataDictionaryExclusionList', 'Procedure');

INSERT INTO DataDictionaryExclusions (ObjectName, ObjectType)
VALUES	('GenerateDataDictionaryAddExclusion', 'Procedure')

